-- Create View

Create view vw_staff_services
AS
SELECT        dbo.Staff.StaffID, dbo.Staff.StaffName, Services.ServiceName, dbo.Staff.Active,dbo.Staff.IsLock
FROM            dbo.Services INNER JOIN
                         dbo.Specialist ON dbo.Services.ServiceID = dbo.Specialist.ServiceID INNER JOIN
                         dbo.Staff ON dbo.Specialist.StaffID = dbo.Staff.StaffID
go
--
Create view vw_cate_services
AS
SELECT        dbo.Category.CatID, dbo.Category.CatName, dbo.Services.ServiceName, dbo.Services.ServiceID, dbo.Services.IsLock, dbo.Services.Price
FROM            dbo.Category INNER JOIN
                         dbo.Services ON dbo.Category.CatID = dbo.Services.CatID

Create view vw_cust_appmaster
AS
SELECT        dbo.AppMaster.AppID, dbo.AppMaster.AppDate, dbo.AppMaster.Status, dbo.Customer.CustID, dbo.Customer.CustName 
FROM            dbo.AppMaster INNER JOIN
                         dbo.Customer ON dbo.AppMaster.CustID = dbo.Customer.CustID

Create view vw_cat_app
AS
SELECT  App_Cat.*, dbo.Category.CatName,dbo.Category.Facilities
From
	(SELECT App.*, dbo.Services.ServiceName, dbo.Services.CatID
	FROM (SELECT dbo.AppMaster.AppID, dbo.AppDetails.ServiceID, dbo.AppDetails.Status
		FROM   dbo.AppMaster INNER JOIN dbo.AppDetails ON dbo.AppMaster.AppID = dbo.AppDetails.AppID) As App
		INNER JOIN dbo.Services ON App.ServiceID = dbo.Services.ServiceID) As App_Cat
		INNER JOIN dbo.Category ON App_Cat.CatID = dbo.Category.CatID

Create view vw_cust_bill
AS
SELECT        dbo.Bill.BillID, dbo.Bill.AppID, dbo.Bill.BillDate, dbo.Bill.TotalAmount, dbo.Customer.CustID, dbo.Customer.CustName 
FROM            dbo.Bill INNER JOIN
                         dbo.Customer ON dbo.Bill.CustID = dbo.Customer.CustID

create view vw_full_app
AS
SELECT App_Ser_Cat_Staff.* , dbo.Customer.CustName
FROM
	(SELECT App_Ser_Cat.*, dbo.Staff.StaffName
	FROM
		(SELECT  App_Ser.*, dbo.Category.CatName,dbo.Category.Facilities
		From
			(SELECT App.*, dbo.Services.ServiceName, dbo.Services.CatID, dbo.Services.Price
			FROM (SELECT dbo.AppMaster.AppID, dbo.AppMaster.Status as SttMaster, dbo.AppMaster.AppDate, dbo.AppMaster.CustID, AppDetails.AppDID, dbo.AppDetails.ServiceID, dbo.AppDetails.StaffID, dbo.AppDetails.Slot , dbo.AppDetails.Status as SttDetail
				FROM   dbo.AppMaster LEFT OUTER JOIN dbo.AppDetails ON dbo.AppMaster.AppID = dbo.AppDetails.AppID) As App
				LEFT OUTER JOIN dbo.Services ON App.ServiceID = dbo.Services.ServiceID) As App_Ser
				LEFT OUTER JOIN dbo.Category ON App_Ser.CatID = dbo.Category.CatID) As App_Ser_Cat
				LEFT OUTER JOIN dbo.Staff ON App_Ser_Cat.StaffID = dbo.Staff.StaffID) As App_Ser_Cat_Staff
				LEFT OUTER JOIN dbo.Customer On App_Ser_Cat_Staff.CustID = dbo.Customer.CustID 